import pandas as pd
from sklearn.preprocessing import MaxAbsScaler
import pymysql
import pymysql.cursors

class MysqlUtils(object):
    def __init__(self):
        host = '127.0.0.1'
        user = "root",
        passwd = "sjk1234",
        db = "scenic",
        port = 3306,
        charset = "utf8"
    

    def get_scenic_data(self):
        """获取数据"""

        
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

        sql = """
        SELECT create_time, COUNT(*) as count FROM order_use GROUP BY HOUR(create_time) ORDER BY HOUR(create_time)
                """
            
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        print(df.head)
    # -*- coding: utf-8 -*-


class MysqlUtils(object):
    def get_scenic_data(self):
        # print(df.head())
        # 格式转换
        date_range = pd.date_range(start='2024-07-01', end='2025-03-02', freq='D')
        hours = range(6, 24)
        full_index = pd.MultiIndex.from_product([date_range, hours], names=['date', 'hour'])
        print(full_index)
        df_full = df.set_index(['date', 'hour']).reindex(full_index, fill_value=0).reset_index()
        # 按天组织数据，每行包含18个小时的股票次数（注意：原文为“检票次数”，但根据上下文和代码逻辑，这里更可能是“股票次数”，需根据实际情况确认）
        df_pivot = df_full.pivot(index='date', columns='hour', values='count')
        #print(df_pivot)
        df_pivot['dow']= df_pivot.index.dayofweek
        df_pivot['month']= df_pivot.index.month
        print(df_pivot)

if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()  